package iglesia.invimosa.lyrics.manager.datalayer;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.ListIterator;

import javax.swing.JOptionPane;

public class LyricsManagerData implements LyricsManagerOperations{
	private String dbname;
	private String username;
	private String password;
	private Connection conn;
	private CallableStatement cs;
	private String url;
	
	public LyricsManagerData(String _dbname, String _username, String _password) {
		this.dbname = _dbname;
		this.username = _username;
		this.password = _password;
		
		url = "jdbc:mysql://"+dbname+"";
		
		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			conn = DriverManager.getConnection(url, username, password);
			
		} catch (InstantiationException e) {
			JOptionPane.showMessageDialog( null, "InstantiationException", "Driver not found", JOptionPane.ERROR_MESSAGE );
			System.exit(0);
		} catch (IllegalAccessException e) {
			JOptionPane.showMessageDialog( null, "IllegalAccessException", "Driver not found", JOptionPane.ERROR_MESSAGE );
			System.exit(0);
		} catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog( null, "ClassNotFoundException", "Driver not found", JOptionPane.ERROR_MESSAGE );
			System.exit(0);
		} catch (SQLException e) {
			JOptionPane.showMessageDialog( null, e.getMessage(), "SQL Exception", JOptionPane.ERROR_MESSAGE );
			System.exit(0);
		}
	}

	@Override
	public boolean createNewList(String name, ArrayList<Songs> songs) {
		try {
			cs = conn.prepareCall("{call sp_createNewList(?)}");
			cs.setString(1, name);
			cs.registerOutParameter(1, java.sql.Types.INTEGER);
			
			cs.execute();
			int tableId = cs.getInt(1);
			
			ListIterator li = songs.listIterator();
			
			while(li.hasNext()){
				cs = conn.prepareCall("{call sp_addSongToList(?)}");
				cs.setInt(1, ((Songs)li.next()).getID());
				cs.execute();
			}
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteArtist(int artistId) {
		try {
			cs = conn.prepareCall("{call sp_deleteArtist(?)}");
			cs.setInt(1, artistId);
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteListOfReproduction(int listId) {
		try {
			cs = conn.prepareCall("{call sp_deleteListOfReproduction(?)}");
			cs.setInt(1, listId);
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteSong(String song, String artist) {
		try {
			cs = conn.prepareCall("{call sp_deleteSong(?,?)}");
			cs.setString(1, song);
			cs.setString(2, artist);
			
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public ResultSet getAllSongTypes() {
		try {
			cs = conn.prepareCall("{call sp_getListOfGenre}");
			return cs.executeQuery();
		} catch (SQLException e) {		
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public int getListId(String listName) {
		try {
			cs = conn.prepareCall("{call sp_getListId(?)}");
			cs.setString(1, listName);
			cs.registerOutParameter(1, java.sql.Types.INTEGER);
			
			cs.execute();
			return cs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public ResultSet getListOfArtists() {
		try {
			cs = conn.prepareCall("{call sp_getListOfArtists}");
			return cs.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}	
	}

	@Override
	public ResultSet getLyricsFromSongName(String songName) {
		try {
			cs = conn.prepareCall("{call sp_getLyricsFromSongName(?)}");
			return cs.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public ResultSet getReproductionList(String listName) {
		try {
			cs = conn.prepareCall("{call sp_getReproductionList(?)}");
			return cs.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
	}

	@Override
	public int getSongId(Songs song) {
		try {
			cs = conn.prepareCall("{sp_getSongId(?,?)}");
			cs.setString(1, song.getName());
			cs.setString(1, song.getArtist());		
			cs.registerOutParameter(1, java.sql.Types.INTEGER);
			
			cs.execute();
			return cs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public int getSongId(String name) {
		try {
			cs = conn.prepareCall("{sp_getSongId(?)}");
			cs.setString(1, name);
			cs.registerOutParameter(1, java.sql.Types.INTEGER);
			
			cs.execute();
			return cs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		
	}

	@Override
	public String getSongType(String songName) {
		try {
			cs = conn.prepareCall("{call sp_getSongType(?)}");
			cs.setString(1, songName);
			cs.registerOutParameter(1, java.sql.Types.NVARCHAR);
			
			cs.execute();
			return cs.getString(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public ResultSet getSongsByArtist(String artist) {
		try {
			cs = conn.prepareCall("{sp_getSongsByArtist(?)}");
			cs.setString(1, artist);
			return cs.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}	
	}

	@Override
	public ResultSet getSongsFromType(String songType) {
		try {
			cs = conn.prepareCall("{call sp_getSongsFromType(?)}");
			cs.setString(1, songType);
			return cs.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}	
	}

	@Override
	public int insertArtist(String artistName) {
		try {
			cs = conn.prepareCall("{call sp_insertArtist(?,?)}");
			cs.setString(1, artistName);
			cs.registerOutParameter(2, java.sql.Types.INTEGER);
			cs.execute();
			
			return cs.getInt(2);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		
	}

	@Override
	public boolean insertNewSong(String songName, int songType,
			int artist, String lyrics, ArrayList<String> tags) {
		
		try {
			cs = conn.prepareCall("{call sp_insertNewSong(?,?,?,?,?)}");
			java.sql.Array songTags = conn.createArrayOf("String", tags.toArray());
			
			cs.setString(1, songName);
			cs.setInt(2, songType);
			cs.setInt(3, artist);
			cs.setString(4, lyrics);
			cs.setArray(5, songTags);
			
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}	
	}

	@Override
	public boolean insertSongType(String songType) {
		try {
			cs = conn.prepareCall("{call sp_insertSongType(?)}");
			cs.setString(1, songType);
			
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean modifyArtistName(int id, String newName) {
		try {
			cs = conn.prepareCall("{call sp_modifyArtistName(?,?)}");
			cs.setInt(1, id);
			cs.setString(2, newName);
			
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean modifySongName(int id, String newName) {
		try {
			cs = conn.prepareCall("{call sp_modifySongName(?,?)}");
			cs.setInt(1, id);
			cs.setString(2, newName);
			
			return cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean modifySongTypeName(int id, String newName) {
		try {
			cs = conn.prepareCall("{call sp_modifySongTypeName(?,?)}");
			cs.setInt(1, id);
			cs.setString(2, newName);
			
			return cs.execute();		
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public int getArtistId(String name) {
		try {
			cs = conn.prepareCall("{call sp_getArtistId(?)}");
			cs.setString(1, name);
			cs.registerOutParameter(1, java.sql.Types.INTEGER);
			
			cs.execute();
			return cs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public int getSongTypeId(String name) {
		try {
			cs = conn.prepareCall("{call sp_getSongTypeId(?)}");
			cs.setString(1, name);
			cs.registerOutParameter(1, java.sql.Types.INTEGER);
			
			cs.execute();
			return cs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		
	}

	@Override
	public ResultSet getSongByName(String name) {
		try {
			cs = conn.prepareCall("{call sp_getSongByeName(?)}");
			cs.setString(1, name);
			
			return cs.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		
	}
}
